from docx import Document
from docx.shared import Pt
from docx.oxml.ns import qn
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
import re

def generate_invoice_word(data, output_path):
    doc = Document()
    # 设置中文字体
    style = doc.styles['Normal']
    style.font.name = '宋体'
    style._element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')
    style.font.size = Pt(10.5)

    # 标题
    title = doc.add_heading('Commercial Invoice', level=0)
    title.alignment = 1  # 居中
    subtitle = doc.add_paragraph('商业发票', style='Normal')
    subtitle.alignment = 1  # 居中

    # 表格
    table = doc.add_table(rows=12, cols=7)
    table.style = 'Table Grid'

    # 第一行
    table.cell(0, 0).merge(table.cell(0, 2))
    table.cell(0, 0).text = 'Waybill NO\n运单号: {}'.format(data['waybill_no'])
    table.cell(0, 3).merge(table.cell(0, 6))
    table.cell(0, 3).text = 'Date of Exportation\n出口日期: {}'.format(data['date_of_exportation'])
    

    # # 第二行
    table.cell(1, 0).merge(table.cell(1, 2))
    table.cell(1, 0).text = 'Export company\n发件公司名:'
    table.cell(1, 3).merge(table.cell(1, 6))
    table.cell(1, 3).text = 'Contact person\n发件联系人:'


    # # 第三行
    table.cell(2, 0).merge(table.cell(2, 2))
    table.cell(2, 0).text = 'Export Address:\n发件地址:\nFloor 1, Building 7, Alley 13, Luozuxia Village, Luozu Community, Baoan District, Shenzhen, China'
    table.cell(2, 3).merge(table.cell(2, 6))
    table.cell(2, 3).text = 'Phone, Fax or E-mail\n电话: 0086 13411260416'

    # # 第四行
    table.cell(3, 0).merge(table.cell(3, 2))
    table.cell(3, 0).text = 'Importer company\n收件公司名:\n{}'.format(data['company_name'])
    table.cell(3, 3).merge(table.cell(3, 6))
    table.cell(3, 3).text = 'Contact person\n收件联系人: {}'.format(data['import_contact'])
   

    # # 第五行
    table.cell(4, 0).merge(table.cell(4, 6))
    table.cell(4, 0).text ='Import Address:\n收件地址:\n{}'.format(data['import_address']+","+data.get('import_city', '')+","+data.get('import_province', '')+","+data.get('import_country',''))

    # # 第六行
    table.cell(5, 0).merge(table.cell(5, 2))
    table.cell(5, 0).text = 'Phone, Fax or E-mail:\n电话: {}'.format(data['import_phone'])
    table.cell(5, 3).merge(table.cell(5, 6))
    table.cell(5, 3).text = 'Zip code\n邮编: {}'.format(data['import_zip'])

     # # 第七行
    table.cell(6, 0).merge(table.cell(6, 1))
    table.cell(6, 0).text = 'Country of Origin\n原产国: China'
    table.cell(6, 2).merge(table.cell(6, 4))
    table.cell(6, 2).text = 'Country of Export\n出口国: China'
    table.cell(6, 5).merge(table.cell(6, 6))
    table.cell(6, 5).text = 'Country of Import\n进口国: USA'
    


    # # 第七行（表头）
    headers = [
        'No. of Pack\n件数',
        'Descriptions\n货物描述\n(HS CODE: 3923210000)',
        'Materials\n材料成份',
        'Application\n产品用途（包装）',
        'Qty\n数量',
        'Unit Price\n单价(USD)',
        'Total Price\n总价(USD)'
    ]
    for i, h in enumerate(headers):
        table.cell(7, i).text = h

    # 动态插入商品数据，至少保留两行
    items = data.get('items', [])
    min_rows = 3
    item_rows = max(len(items), min_rows)
    for idx in range(item_rows):
        row = 8 + idx
        if idx < len(items):
            item = items[idx]
            table.cell(row, 0).text = str(item.get('no_of_pack', ''))
            table.cell(row, 1).text = item.get('description', '')
            table.cell(row, 2).text = item.get('material', '')
            table.cell(row, 3).text = item.get('application', '')
            table.cell(row, 4).text = str(item.get('qty', ''))
            table.cell(row, 5).text = str(item.get('unit_price', ''))
            table.cell(row, 6).text = str(item.get('total_price', ''))
        else:
            for col in range(7):
                table.cell(row, col).text = ''

    # 合计行，始终在数据行后
    sum_row = 8 + item_rows
    table.cell(sum_row, 0).merge(table.cell(sum_row, 2))
    table.cell(sum_row, 0).text = 'Total No. of Package\n总件数: {}'.format(data['total_no_of_package'])
    table.cell(sum_row, 3).merge(table.cell(sum_row, 6))
    # 合计金额自动计算
    total_price = sum(float(item.get('total_price', 0) or 0) for item in items)
    table.cell(sum_row, 3).text = 'Total Price {:.2f}\n总价(USD)'.format(total_price)
    # # 备注
    doc.add_paragraph('备注：请用英文填写，材料成份、产品用途必填')

    doc.save(output_path)

def generate_invoice_excel(data, output_path):
    import openpyxl
    from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
    from openpyxl.utils import get_column_letter
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = '下单模板'

    # 样式
    thin = Side(border_style="thin", color="000000")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)
    center = Alignment(horizontal='center', vertical='center', wrap_text=True)
    left = Alignment(horizontal='left', vertical='center', wrap_text=True)
    red_bold = Font(color='FF0000', bold=True)
    black_bold = Font(color='000000', bold=True)
    gray_fill = PatternFill("solid", fgColor="EDEDED")
    yellow_fill = PatternFill("solid", fgColor="FFFF99")
    title_fill = PatternFill("solid", fgColor="C2C9B5")

    # 设置列宽
    col_widths = [20, 16, 16, 8, 13, 16, 16, 16, 16, 10, 8, 10, 16, 21, 16, 16, 16]
    for i, w in enumerate(col_widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w
    #ws.column_dimensions['A'].width = 0.1
    ws.column_dimensions['G'].width = 22
    ws.column_dimensions['L'].width = 22
    # 模板编号
    ws['A1'] = '模板编号2268'
    ws['A1'].font = red_bold
    #ws['A1'].alignment = center
    # ws['A1'].border = border

    # 标题
    ws.merge_cells('B1:O1')
    ws['B1'] = '下单模板'
    ws['B1'].font = Font(size=18, bold=True)
    ws['B1'].alignment = center
    # ws['B1'].border = border


    ws.merge_cells('A2:F2')
    ws['A2'] = '发件人信息'
    ws['A2'].font = black_bold
    ws['A2'].fill = title_fill

    ws.merge_cells('G2:O2')
    ws['G2'] = '收件人信息'
    ws['G2'].font = black_bold
    ws['G2'].fill = title_fill

    # 发件人信息左侧字段
    ws['A3'] = 'Ref/批次跟踪号:'
    ws['A3'].font = red_bold
    ws['A3'].border = border

    ws['E3'] = '箱数/件数:'
    ws['E3'].font = red_bold
    ws['E3'].border = border

    ws['F3'] = data.get('total_no_of_package', '')

    ws['A4'] = 'Shipper发件人:'
    ws['A4'].border = border

    ws['A5'] = 'Company Name公司名:'
    ws['A5'].border = border

    ws['A6'] = 'Address地址:'
    ws['A6'].border = border

    ws['A7'] = 'City城市名:'
    ws['A7'].border = border

    ws['E7'] = 'Province省:'
    ws['E7'].border = border



    ws['A8'] = 'Postal Code邮编:'
    ws['A8'].border = border


    ws['A9'] = '额外服务:'
    ws['A9'].border = border

    ws['A10'] = '备注:'
    ws['A10'].border = border

    ws['E8'] = 'Phone电话:'
    ws['E8'].border = border
    ws['E8'].font = red_bold
   

    # 收件人信息右侧字段
    ws['G3'] = 'Consignee收件人:'
    ws['G3'].font = red_bold
    ws['G3'].border = border
    # ws['G3'].border = border
    ws['H3'] = data.get('import_contact', '')
    # ws['H3'].border = border
    ws['G4'] = 'Company Name公司名:'
    ws['G4'].font = red_bold
    ws['G4'].border = border
    # ws['G4'].border = border
    ws['H4'] = data.get('company_name', '')
    # ws['H4'].border = border
    ws['G5'] = 'Country国家/区域:'
    ws['G5'].font = red_bold
    ws['G5'].border = border
    # ws['G5'].border = border
    ws['H5'] = data.get('import_country', '')

    ws['L5'] = 'PostalCode邮编:'
    ws['L5'].font = red_bold
    ws['L5'].border = border
    # ws['M5'].border = border
    ws['M5'] = data.get('import_zip', '')
    # ws['N5'].border = border

    ws['G6'] = 'Address地址:'
    ws['G6'].font = red_bold
    ws['G6'].border = border
    # ws['G6'].border = border
    ws.merge_cells('H6:O6')
    ws['H6'] = data.get('import_address', '')
    # ws['H6'].border = border

    ws['G7'] = 'City城市名:'
    ws['G7'].font = red_bold
    ws['G7'].border = border
    # ws['G7'].border = border
    ws['H7'] = data.get('import_city', '')
    # ws['H7'].border = border
    ws['L7'] = 'Province州/省:'
    ws['L7'].font = red_bold
    ws['L7'].border = border
    # ws['M7'].border = border
    ws['M7'] = data.get('import_province', '')
    # ws['N7'].border = border

   
    ws['G8'] = 'Phone电话:'
    ws['G8'].font = red_bold
    ws['G8'].border = border
    ws['H8'] = data.get('import_phone', '')
    # ws['H8'].border = border

    ws['L8'] = 'FBA仓库代码:'
    ws['L8'].border = border
    # ws['M8'].border = border
    ws['M8'] = '*'

    ws['G9'] = 'VAT税号:'
    ws['G9'].border = border
    # ws['G9'].border = border
    ws.merge_cells('H9:O9')
    ws['H9'] = '[Vat税号]'
   

    # 明细表头
    header_row = 11
    headers = [
        'FBA编号', 'Reference ID', '材积CM(长*宽*高)严格按此格式', '相同信息箱数', '单箱毛重(KGS)', '海关编码HSCODE',
        '中文品名', '英文品名', '单箱数量(PCS)',  '单个物品价格(USD)', '品牌', '规格型号', '材质', '用途', '产品图片'
    ]
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=header_row, column=col)
        cell.value = h
        cell.font = red_bold
        cell.alignment = center
        cell.border = border
        if h == '海关编码HSCODE':
            cell.fill = yellow_fill
        else:
            cell.fill = gray_fill

    # 明细数据
    items = data.get('items', [])
    for idx, item in enumerate(items):
        desc = item.get('description', '')
        hscode = ''
        import re
        m = re.match(r'^(.*)\(Hscode:(\d+)\)$', desc)
        if m:
            desc = m.group(1)
            hscode = m.group(2)
        else:
            hscode = item.get('hscode', '')
        row = header_row + 1 + idx
        ws.cell(row=row, column=1, value='')  # FBA编号
        ws.cell(row=row, column=2, value='')  # Reference ID
        ws.cell(row=row, column=3, value='')  # 材积
        ws.cell(row=row, column=4, value='')  # 相同信息箱数
        ws.cell(row=row, column=5, value='')  # 单箱毛重
        ws.cell(row=row, column=6, value=hscode)
        ws.cell(row=row, column=7, value=desc)
        ws.cell(row=row, column=8, value=desc)
        ws.cell(row=row, column=9, value=item.get('qty', ''))
        ws.cell(row=row, column=10, value=item.get('unit_price', ''))
        ws.cell(row=row, column=11, value='[品牌]')
        ws.cell(row=row, column=12, value='[规格]')
        ws.cell(row=row, column=13, value=item.get('material', ''))
        ws.cell(row=row, column=14, value=item.get('application', ''))
        ws.cell(row=row, column=15, value='')  # 产品图片
        for col in range(1, 16):
            cell = ws.cell(row=row, column=col)
            cell.alignment = center
            cell.border = border
    # 设置所有已用行的高度
    for i in range(1, ws.max_row + 1):
        ws.row_dimensions[i].height = 20
    wb.save(output_path)
    

# 示例调用
data = {
    'waybill_no': '6632948000',
    'date_of_exportation': '4-16',
    'company_name':'Shenzhen HengMao Pack Co.,Ltd',
    'import_contact': 'Albert Cruz',
    'import_address': '2125 quimby ave Bronx ny 10473',
    'import_phone': '+1 954-559-8137',
    'import_zip': '10473',
    'no_of_pack': 1,
    'qty': '504pcs',
    'total_no_of_package': 1,
    'items': [
        {'no_of_pack': 1, 'description': 'Plasticbottle', 'material': 'PP', 'application': 'packing', 'qty': '504pcs', 'unit_price': '0.34', 'total_price': '171.36'}
    ]
}
generate_invoice_word(data, 'invoice.docx')